/*
 * This file is part of "Sorting Thoughts" project.
 *
 * Copyright (c) 2010 Hendrik Ebel
 * All Rights Reserved.
 *
 * Please visit http://code.google.com/p/sorting-thoughts/ for more information.
 *
 * --LICENSE NOTICE--
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 3
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; If not, see <http://www.gnu.org/licenses/>.
 * --LICENSE NOTICE--
 */

var Dao = Class.create({
	
	initialize: function(){
        var name = "ext:SortingThoughtsDB";
	    var version = "0.1.0";
	    var displayName = "Sorting Thoughts database";
	    var size = 200000;
	    
	    this.db = openDatabase(name, version, displayName, size);
    },
    
	createThoughtTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "CREATE TABLE IF NOT EXISTS 'thought' (id TEXT PRIMARY KEY, title TEXT, content TEXT, imported INTEGER, modifiedDate TEXT, lastViewedDate TEXT, favorite INTEGER, parent TEXT, rowStyle TEXT, lastSyncDate TEXT, createDate TEXT, timeZoneOffset TEXT, rich INTEGER); ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	createSyncTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "CREATE TABLE IF NOT EXISTS 'sync' (syncId TEXT, allLastSyncDate TEXT, pleaseRestart TEXT); ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	createLockTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "CREATE TABLE IF NOT EXISTS 'lock' (lock TEXT, lockTwo Text); ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	createSettingsTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "CREATE TABLE IF NOT EXISTS 'st_settings' (currentVersion TEXT PRIMARY KEY, timeZoneOffset TEXT, sortingOrder TEXT); ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addTimeZoneOffsetColumn: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE st_settings ADD timeZoneOffset TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addTimeZoneOffsetColumnToThoughtTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE thought ADD timeZoneOffset TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addRichColumnToThoughtTable: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE thought ADD rich INTEGER; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addSortingOrderColumn: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE st_settings ADD sortingOrder TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addRowStyleColumn: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE thought ADD rowStyle TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addLastSyncDateColumn: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE thought ADD lastSyncDate TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	addCreateDateColumn: function(){
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
			var sql = "ALTER TABLE thought ADD createDate TEXT; ";
			
	        this.db.transaction((function(transaction) {
	            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbIgnoreErrorHandler.bind(this));
	        }).bind(this));
	    }
	},
	
	insertWelcomeContent: function(){
		if (SortingThoughts.firstStart) {
			var thought = new Thought("", "", "", "", "", "", "", 0, 0, "");
		    thought.initNewThought();
		    
		    var title = $L("Welcome to Sorting Thoughts");
		    var content = $L("Thanks for downloading of Sorting Thoughts - have fun to collect your thoughts...");
			
		    if (!this.db) {
		        Mojo.Log.error("Could not access sorting thoughts database");
		    } else {
		       var sql = "INSERT INTO thought (id, title, content, imported, modifiedDate, lastViewedDate, favorite, parent, rowStyle, createDate, timeZoneOffset, rich) VALUES (?,?,?,?,?,?,?,?,?,?,?,?);"
		       this.db.transaction((function(transaction) {
		            transaction.executeSql(sql, [thought.id, title, content, thought.imported, thought.mDate, thought.vDate, thought.favorite, thought.parent, thought.rowStyle, thought.cDate, thought.tzOffset, 1], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
		        }).bind(this));
		    }
		    
		    Mojo.Log.info("Welcome entry inserted");
		    SortingThoughts.firstStart = false;
		    SortingThoughts.Cookie.storeCookie();
		}
	},
	
	insertSettingsContent: function(timeZoneOffsetInMillis, sortingOrder) {
		
		    if (!this.db) {
		        Mojo.Log.error("Could not access sorting thoughts database");
		    } else {
		       var sql = "INSERT INTO st_settings (currentVersion, timeZoneOffset, sortingOrder) VALUES (?,?,?);"
		       this.db.transaction((function(transaction) {
		            transaction.executeSql(sql, [SortingThoughts.versionString, timeZoneOffsetInMillis, sortingOrder], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
		        }).bind(this));
		    }
		    
		    Mojo.Log.info("Version content inserted");
	},
	
	insertLock: function() {
		    if (!this.db) {
		        Mojo.Log.error("Could not access sorting thoughts database");
		    } else {
		       var sql = "INSERT INTO lock (lock, lockTwo) VALUES (?, ?);"
		       this.db.transaction((function(transaction) {
		            transaction.executeSql(sql, [SortingThoughts.curLock, SortingThoughts.curLockTwo], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
		        }).bind(this));
		    }
		    
		    Mojo.Log.info("lock inserted");
	},
	
	removeLock: function() {
		    if (!this.db) {
		        Mojo.Log.error("Could not access sorting thoughts database");
		    } else {
		       var sql = "DELETE FROM lock;"
		       this.db.transaction((function(transaction) {
		            transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
		        }).bind(this));
		    }
		    
		    Mojo.Log.info("lock removed");
	},
	
	// only for testing
	insertTestContent: function(number) {
		
		    if (!this.db) {
		        Mojo.Log.error("Could not access sorting thoughts database");
		    } else {
				var thought = new Thought("", "", "", "", "", "", "", 0, 0, "");
			    thought.initNewThought();
				thought.id = thought.id + 10;
				thought.title = number + ": Test thought";
				thought.content = "Die Wall Street beendet den Tag deutlich im Minus. Dabei sieht alles gut aus in den USA, sagt Gaske. Die Konsumenten gehen wieder einkaufen, die Unternehmen verdienen wieder Geld, die Krise scheint vorerst überstanden. Selbst der Häusermarkt, einst Auslöser der Finanzkrise, stabilisiert sich etwas. Doch Jubel kommt trotzdem nicht auf. Der Grund dafür ist der kleine Staat im Süden Europas, den die meisten Amerikaner nur vom Hörensagen kennen. Kein Wunder, dass die Regierung von Präsident Barack Obama die Europäer immer wieder dazu gedrängt hat, schneller zu handeln statt wie im Fall von Deutschland die im globalen Maßstab völlig unbedeutende Landtagswahl in Nordrhein-Westfalen abzuwarten. Das gab Obama auch Angela Merkel zu verstehen, als die beiden am Wochenende mehrmals miteinander telefonierten. Wir waren uns einig darin, dass es einer starken politischen Antwort der betroffenen Staaten und einer starken finanziellen Antwort der internationalen Gemeinschaft bedarf, sagte Obama hinterher. Er habe Merkel die Unterstützung der USA dabei zugesichert. ";
		        
				var sql = "INSERT INTO thought (id, title, content, imported, modifiedDate, lastViewedDate, favorite, parent, rowStyle, createDate, timeZoneOffset, rich) VALUES (?,?,?,?,?,?,?,?,?,?,?,);"
				this.db.transaction((function(transaction){
					transaction.executeSql(sql, [thought.id, thought.title, thought.content, thought.imported, thought.mDate, thought.vDate, thought.favorite, thought.parent, thought.rowStyle, thought.cDate, thought.tzOffset, 1], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
				}).bind(this));
				
	    		Mojo.Log.info("Test content inserted: " + number);
		    }
		    
	},
	
	insertNewThought: function() {
		var thought = new Thought("", "", "", "", "", "", "", 0, 0, "");
		thought.initNewThought();
	
		if (!this.db) {
			Mojo.Log.error("Could not access sorting thoughts database");
		} else {
			var sql = "INSERT INTO thought (id, title, content, imported, modifiedDate, lastViewedDate, favorite, parent, rowStyle, createDate, timeZoneOffset, rich) VALUES (?,?,?,?,?,?,?,?,?,?,?,?);"
			this.db.transaction((function(transaction){
				transaction.executeSql(sql, [thought.id, thought.title, thought.content, thought.imported, thought.mDate, thought.vDate, thought.favorite, thought.parent, thought.rowStyle, thought.cDate, thought.tzOffset, 1], this.dbSuccessHandler.bind(this), this.dbInsertErrorHandler.bind(this));
			}).bind(this));
			Mojo.Log.info("New thought inserted: " + thought.id);
			return thought;
		}
	},
	
	deleteThought: function(thoughtId){
        if (!this.db || !thoughtId) {
            Mojo.Log.error("Could not access sorting thoughts database or have no ID for delete!");
        } else {
            var sql = "DELETE FROM thought WHERE id = ?; "
            this.db.transaction((function(transaction){
                transaction.executeSql(sql, [thoughtId], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
            }).bind(this));
			Mojo.Log.info("Thought deleted: " + thoughtId);
        }
	},
	
	updateThought: function(thought) {
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
				var rich = 1;
                var sql = "UPDATE thought SET title=?, content=?, modifiedDate=?, lastViewedDate=?, rich=? WHERE ID = ?";
                
                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [thought.title, thought.content, thought.mDate, thought.vDate, rich, thought.id], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Thought updated: " + thought.id);
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	updateRowStyle: function(rowStyle, id) {
	    if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
				var now = new Date();
        		var mDate = now.getTime();
				var sql = "UPDATE thought SET rowStyle=?, modifiedDate=? WHERE ID = ?";
                
                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [rowStyle, mDate, id], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Thought row style updated: " + rowStyle);
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	updateMissingRowStyle: function() {
		if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
				var rowStyle = "whiteColor"; // default value
                var sql = "UPDATE thought SET rowStyle=? WHERE rowStyle is null";
                
                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [rowStyle], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Missing thought row style updated");
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	updateMissingTimezoneOffset: function() {
		if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
				var now = new Date();
        		var offset = now.getTimezoneOffset() * 60000;
                var sql = "UPDATE thought SET timeZoneOffset=?, modifiedDate=modifiedDate - ?,createDate = createDate - ?, lastViewedDate = lastViewedDate - ? WHERE timeZoneOffset is null";
                
                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [offset, offset, offset, offset], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Missing time zone offset updated for all thoughts");
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	updateMissingCreateDate: function() {
		if (!this.db) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
                var sql = "UPDATE thought SET createDate=id WHERE createDate is null";

                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Missing thought create date updated");
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	updateSettings: function(timeZoneOffsetInMillis, sortingOrder) {
		if (!this.db || timeZoneOffsetInMillis == null || sortingOrder == null) {
	        Mojo.Log.error("Could not access sorting thoughts database");
	    } else {
	        try {
                var sql = "UPDATE st_settings SET timeZoneOffset=?, sortingOrder=? WHERE currentVersion=?";
				
                this.db.transaction((function(transaction) {
                    transaction.executeSql(sql, [timeZoneOffsetInMillis, sortingOrder, SortingThoughts.versionString], this.dbSuccessHandler.bind(this), this.dbErrorHandler.bind(this));
                }).bind(this));
				Mojo.Log.info("Thought settings updated");
	        } catch (e) {
	             Mojo.Log.error("Update error: " + e);
	        }
	    }
	},
	
	getCurrentSortingOrderName: function() {
		var sortingOrderName = "sortByCdate"; // default
		if (SortingThoughts.sortByColor) {
			sortingOrderName = "sortByColor";
		} else if (SortingThoughts.sortByMdate) {
			sortingOrderName = "sortByMdate";
		} else if (SortingThoughts.sortByVdate) {
			sortingOrderName = "sortByVdate";
		} else if (SortingThoughts.sortByTitle) {
			sortingOrderName = "sortByTitle";
		}
		
		return sortingOrderName;
	},

	dbSuccessHandler: function(transaction, results){
	    Mojo.Log.info("Successfully SQL execution");
	},
	
	dbErrorHandler: function(transaction, error){
	    Mojo.Log.error("Could not execute SQL: " + error.message);
	},
	
	dbInsertErrorHandler: function(transaction, error){
	    Mojo.Log.error("Could not insert data in Sorting Thoughts DB: " + error.message);
	},
	
	dbIgnoreErrorHandler: function(transaction, info){
	    Mojo.Log.info("Could not insert data in Sorting Thoughts DB: " + info.message);
	}
    
});
